package com.order.dao;

import java.sql.*;
import java.util.*;
public class SeatDButil
{
	static String driver="org.gjt.mm.mysql.Driver";
	 static String url="jdbc:mysql://localhost:3306/test";
	 static	String user="root";	
		public static void initseata(String seatx,String seaty)
		{
			try
			{
				Class.forName(driver);
				Connection con=DriverManager.getConnection(url,user,"");
				Statement st=con.createStatement();
				String sql=null;
				int x=Integer.parseInt(seatx);
				int y=Integer.parseInt(seaty);
				for(int i=0;i<x;i++)
				{
					for(int j=1;j<=y;j++)
					{
						String id='a'+String.valueOf(i)+String.valueOf(j);
						
						sql="insert into desk values('"+id+"', '"+id+"',null)";
						st.executeUpdate(sql);
					}
				}
				st.close();
				con.close();
				
			}catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		
		public static void initseatb(String b)
		{
			
			try
			{
				Class.forName(driver);
				Connection con=DriverManager.getConnection(url,user,"");
				Statement st=con.createStatement();
				String sql=null;
				int x=Integer.parseInt(b);
				System.out.print(x);
				for(int i=1;i<=x;i++)
				{
					String id=null;
					if(i<10)
					{
					id="b0"+String.valueOf(i);
					}
					else
					{
					 id='b'+String.valueOf(i);
					}
						sql="insert into desk values('"+ id + "','"+id+"',null);";
						st.executeUpdate(sql);
					
				}
				
				st.close();
				con.close();
				
			}catch(Exception e)
			{
				e.printStackTrace();
			}
			
		}
		public static String getmaxseat(String xb)
		{
			String str=null;
			try
			{
				Class.forName(driver);
				Connection con=DriverManager.getConnection(url,user,"");
				Statement st=con.createStatement();
				String sql=null;
				if(xb.equals("a"))
				{
				sql="select max(deskid) from desk where deskid like 'a%';";
				}
				else if(xb.equals("b"))
				{
				sql="select max(deskid) from desk where deskid like 'b%';";
				}
				ResultSet rs=st.executeQuery(sql);
			    rs.next();
			    str=rs.getString(1);
			    System.out.println(str);
			    rs.close();
			    st.close();
			    con.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
			return str;
		}
		public static List<String[]> getidsa()
		{
			List<String[]> ll=new ArrayList<String[]>();
			try
			{
				Class.forName(driver);
				Connection con=DriverManager.getConnection(url,user,"");
				Statement st=con.createStatement();
				ResultSet rs=st.executeQuery("select deskid from desk where deskid like 'a%';");
				int i=0;
				while(rs.next())
				{
					String[] s=new String[1];
					s[0]=rs.getString(1);
					ll.add(s);
				}
				rs.close();
				st.close();
				con.close();
			}catch(Exception e)
			{
				e.printStackTrace();
			}
			return ll;
		}
		public static List<String[]> getidsb()
		{
			List<String[]> ll=new ArrayList<String[]>();
			try
			{
				Class.forName(driver);
				Connection con=DriverManager.getConnection(url,user,"");
				Statement st=con.createStatement();
				String sql="select deskid from desk where deskid like 'b%';";
				ResultSet rs=st.executeQuery(sql);
				while(rs.next())
				{
					String[] s=new String[1];
					s[0]=rs.getString(1);
					ll.add(s);
				}
				rs.close();
				st.close();
				con.close();
			}catch(Exception e)
			{
				e.printStackTrace();
			}
			return ll;
		}
	    public static void addseat(String sid)
	    {
	    	try
	    	{
	    		Class.forName(driver);
				Connection con=DriverManager.getConnection(url,user,"");
				Statement st=con.createStatement();
				st.executeUpdate("insert into desk values('"+sid+"','"+sid+"',null)");
				st.close();
				con.close();
	    	}
	    	catch(Exception e)
	    	{
	    		e.printStackTrace();
	    	}
	    }
        public static boolean ifhave(String xb,String no)
        {
        	boolean str=false;
        	try
	    	{
	    		Class.forName(driver);
				Connection con=DriverManager.getConnection(url,user,"");
				Statement st=con.createStatement();
				String id=null;
				String sql=null;
				if(xb.equals("a"))
				{
					id='a'+no;
					sql="select * from desk where deskid = '"+id+"'";
				}
				else if(xb.equals("b"))
				{
					id='b'+no;
					sql="select * from desk where deskid='"+id+"'";
				}
				ResultSet rs=st.executeQuery(sql);
				System.out.print("========");
			    rs.next();
			    String s=rs.getString(1);
			        if(!s.equals(null))
			    	{
			    		str=true;
			    	}
			    rs.close();
			    st.close();
			    con.close();
	    	}catch(Exception e)
	    	{
	    		e.printStackTrace();
	    	}
        	return str;
        }
        public static void delseat(String xb,String no)
        {
        	try
	    	{
	    		Class.forName(driver);
				Connection con=DriverManager.getConnection(url,user,"");
				Statement st=con.createStatement();
				String seatid=xb+no;
				String sql="delete from desk where deskid='"+seatid+"'";
				st.executeUpdate(sql);
				st.close();
				con.close();
	    	}
        	catch(Exception e)
        	{
        		e.printStackTrace();
        	}
        }
        public static void main(String args[])
        {
        	System.out.print(" =========");
        	List<String[]> str=SeatDButil.getidsb();
        	System.out.print(str.size());
        	for(String[] s:str)
        	{
        		System.out.print(s[0]);
        		
        	}
         }
        public static void deldesk(String str)
        {
           	try
    	    {
    	        Class.forName(driver);
    			Connection con=DriverManager.getConnection(url,user,"");
    			Statement st=con.createStatement();
    			String sql=null;
    			if(str.equals("a"))
    			{
    				sql="delete from desk where deskid like 'a%';";
    			}
    			else if(str.equals("b"))
    			{
    				sql="delete from desk where deskid like 'b%';";
    			}
    			st.executeUpdate(sql);
    			st.close();
    			con.close();
    	    }catch(Exception e)
    	    {
    	    	e.printStackTrace();
    	    }
           	
        }
}    